package org.vy.dal.dao.member;

import java.util.List;

import org.vy.dal.QueryManager;
import org.vy.dal.dao.ViewVyMemberDO;

public class SearchMemberDAO {

	private static final String FIND_BY_PARAM = "SELECT * FROM <TABLE> WHERE 1 = 1 ";

	@SuppressWarnings("unchecked")
	public static List<ViewVyMemberDO> findByParam(ViewVyMemberDO dto)
			throws Exception {

		StringBuilder sb = new StringBuilder(FIND_BY_PARAM);
		if(dto.getVyId() != null){
			sb.append(" AND VY_ID LIKE '%"+ dto.getVyId() +"%' ");
		}
		if(dto.getFamilyVyId() != null){
			sb.append(" AND FAMILY_VY_ID LIKE '%"+ dto.getFamilyVyId() +"%' ");
		}
		if(dto.getFirstName() != null){
			sb.append(" AND (FIRST_NAME LIKE '%"+ dto.getFirstName() +"%' ");
			sb.append(" OR LAST_NAME LIKE '%"+ dto.getFirstName() +"%' ");
			sb.append(" OR MIDDLE_NAME LIKE '%"+ dto.getFirstName() +"%') ");
		}
		if(dto.getRegionalLangName() != null){
			sb.append(" AND (REGIONAL_LANG_NAME LIKE '%"+ dto.getRegionalLangName().replaceAll("'","''") +"%') ");
		}
		if(dto.getPrtPhone1() != null
				|| dto.getPmtPhone1() != null
				|| dto.getOffPhone1() != null){
			sb.append(" AND PHONE_NUMBER LIKE '%"+ dto.getPhoneNumber() +"%' ");
		}
		if(dto.getPrtPhone1() != null){
			sb.append(" AND (PRT_PHONE1 LIKE '%"+ dto.getPrtPhone1() +"%' ");
			sb.append(" OR PRT_PHONE2 LIKE '%"+ dto.getPrtPhone1() +"%') ");
		}
		if(dto.getPmtPhone1() != null){
			sb.append(" AND (PMT_PHONE1 LIKE '%"+ dto.getPmtPhone1() +"%' ");
			sb.append(" OR PMT_PHONE2 LIKE '%"+ dto.getPmtPhone1() +"%') ");
		}
		if(dto.getOffPhone1() != null){
			sb.append(" AND (OFF_PHONE1 LIKE '%"+ dto.getOffPhone1() +"%' ");
			sb.append(" OR OFF_PHONE2 LIKE '%"+ dto.getOffPhone1() +"%') ");
		}
		if(dto.getEmail() != null){
			sb.append(" AND EMAIL LIKE '%"+ dto.getEmail() +"%' ");
		}
		if(dto.getPrtStreet1() != null){
			sb.append(" AND (PRT_STREET_1 + PRT_STREET_2 LIKE '%"+ dto.getPrtStreet1() +"%' ");
			sb.append(" OR PMT_STREET_1 + PMT_STREET_2 LIKE '%"+ dto.getPrtStreet1() +"%' ");
			sb.append(" OR OFF_STREET_1 + OFF_STREET_2 LIKE '%"+ dto.getPrtStreet1() +"%') ");
		}
		if(dto.getPrtStreetHindi1() != null){
			sb.append(" AND (PRT_STREET_HINDI_1 + PRT_STREET_HINDI_2 LIKE '%"+ dto.getPrtStreet1().replaceAll("'","''") +"%' ");
			sb.append(" OR PMT_STREET_HINDI_1 + PMT_STREET_HINDI_2 LIKE '%"+ dto.getPrtStreet1().replaceAll("'","''") +"%' ");
			sb.append(" OR OFF_STREET_HINDI_1 + OFF_STREET_HINDI_2 LIKE '%"+ dto.getPrtStreet1().replaceAll("'","''") +"%') ");
		}
		if(dto.getPrtCity() != null){
			sb.append(" AND (PRT_CITY LIKE '%"+ dto.getPrtCity() +"%' ");
			sb.append(" OR PMT_CITY LIKE '%"+ dto.getPrtCity() +"%' ");
			sb.append(" OR OFF_CITY LIKE '%"+ dto.getPrtCity() +"%') ");
		}
		if(dto.getPrtDistrictOrCounty() != null){
			sb.append(" AND (PRT_DISTRICT_OR_COUNTY LIKE '%"+ dto.getPrtDistrictOrCounty() +"%' ");
			sb.append(" OR PMT_DISTRICT_OR_COUNTY LIKE '%"+ dto.getPrtDistrictOrCounty() +"%' ");
			sb.append(" OR OFF_DISTRICT_OR_COUNTY LIKE '%"+ dto.getPrtDistrictOrCounty() +"%') ");
		}
		if(dto.getPrtStateOrRegion() != null){
			sb.append(" AND (PRT_STATE_OR_REGION LIKE '%"+ dto.getPrtStateOrRegion() +"%' ");
			sb.append(" OR PMT_STATE_OR_REGION LIKE '%"+ dto.getPrtStateOrRegion() +"%' ");
			sb.append(" OR OFF_STATE_OR_REGION LIKE '%"+ dto.getPrtStateOrRegion() +"%') ");
		}
		if(dto.getPrtCountry() != null){
			sb.append(" AND (PRT_COUNTRY LIKE '%"+ dto.getPrtCountry() +"%' ");
			sb.append(" OR PMT_COUNTRY LIKE '%"+ dto.getPrtCountry() +"%' ");
			sb.append(" OR OFF_COUNTRY LIKE '%"+ dto.getPrtCountry() +"%') ");
		}
		if(dto.getIsUpdestha() != null){
			sb.append(" AND (IS_UPDESTHA = "+ dto.getIsUpdestha() +") ");
		}
		System.out.println(sb.toString());
		return (List<ViewVyMemberDO>) QueryManager.executeListQuery(dto, sb.toString());
	}
}
